﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F4MaterialDtail : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        private DataSet ds;
        private DataViewManager dsView;

        private string totalOrder;
        private string totalReceive;
        private string notReceive;
        private string totalDelivery;
        private string inStore;

        public panel_F4MaterialDtail()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            
            this.ds = new DataSet("Material");

            this.totalOrder = "0";
            this.totalReceive = "0";
            this.notReceive = "0";
            this.totalDelivery = "0";
            this.inStore = "0";
            LoadMaterialList();

            this.F4MaterialOrder.ViewCaption = "Order: -Total Order Quantity: " + this.totalOrder;
            this.F4MaterialReceive.ViewCaption = "Receive: - Total Receive: " + this.totalReceive + "; - Not Receive: " + this.notReceive;
            this.F4MaterialDelivery.ViewCaption = "Delivery: - Total Delivery: " + this.totalDelivery + "; - In Store: " + this.inStore;

            this.partNo.SelectedIndexChanged += partNo_SelectedIndexChanged;
        }

        void partNo_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadData();
        }

        private void LoadMaterialList()
        {
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT RTRIM(LTRIM(t1.PartNo)) AS PartNo, t1.PartEnglishName, t1.BuyingUnit,"
                    + " t1.PackingNote, t2.Price, t2.CurrencyID, t3.EnglishName AS MakerName, t4.SupplierName,"
                    + " RTRIM(LTRIM(t1.Remark)) AS Remark, ISNULL(t1.ROHS, 0) AS ROHS"
                    + " FROM [222_Part] AS t1 LEFT JOIN"
                    + " [241_ImportQuotation] AS t2 ON t1.PartNo = t2.PartNo LEFT JOIN"
                    + " [222_Maker] AS t3 ON t1.MakerID = t3.MakerID LEFT JOIN"
                    + " [222_Supplier] AS t4 ON t1.SupplierID = t4.SupplierID"
                    + " WHERE t1.PartNo IN"
                    + " (SELECT MaterialID FROM [444_MaterialOrderDetail]" 
                    + " UNION "
                    + " SELECT MaterialID FROM [444_MaterialF4Receive]"
                    + " UNION" 
                    + " SELECT MaterialPart FROM [444_MaterialF4Delivery])"
                    + " ORDER BY RTRIM(LTRIM(t1.PartNo))";
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.SelectCommand = sql_cmd;
                da1.TableMappings.Add("Table", "Material");
                da1.Fill(ds);
                dsView = ds.DefaultViewManager;
                sql_cmd.Dispose();
                myConnection.Close();

                this.partNo.DataSource = dsView;
                this.partNo.DisplayMember = "Material.PartNo";
                this.partNo.ValueMember = "Material.PartNo";
                this.partNo.AutoCompleteSource = AutoCompleteSource.ListItems;

                this.partName.DataBindings.Add("Text", dsView, "Material.PartEnglishName");
                this.unitID.DataBindings.Add("Text", dsView, "Material.BuyingUnit");
                this.packingNote.DataBindings.Add("Text", dsView, "Material.PackingNote");
                this.priceValue.DataBindings.Add("Text", dsView, "Material.Price");
                this.currencyID.DataBindings.Add("Text", dsView, "Material.CurrencyID");
                this.makerName.DataBindings.Add("Text", dsView, "Material.MakerName");
                this.supplierName.DataBindings.Add("Text", dsView, "Material.SupplierName");
                this.remarkText.DataBindings.Add("Text", dsView, "Material.Remark");
                this.roHS.DataBindings.Add("Checked", dsView, "Material.ROHS");
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
            }
        }
        
        //////////////////////////////////////////////////
        /// Load data from database to grid
        //////////////////////////////////////////////////
        private void LoadData()
        {
            if (this.partNo.SelectedIndex >= 0)
            {
                this.totalOrder = "0";
                this.totalReceive = "0";
                this.notReceive = "0";
                this.totalDelivery = "0";
                this.inStore = "0";

                DataSet orderDataSet = new DataSet();
                DataSet receiveDataSet = new DataSet();
                DataSet deliveryDataSet = new DataSet();
                DataSet summaryDataSet = new DataSet();

                SQLManagement mySQLManagement = new SQLManagement();
                mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
                string sql_order = "SELECT OrderNo, MaterialID, Quantity, DeadLine"
                    + " FROM [444_MaterialOrderDetail]"
                    + " WHERE MaterialID = '" + this.partNo.Text + "' ORDER BY DeadLine";
                string sql_receive = "SELECT InvoiceNo, MaterialID, MaterialF4ReceiveQuantity, MaterialF4ReceiveDate"
                    + " FROM [444_MaterialF4Receive] WHERE MaterialID = '" + this.partNo.Text + "' ORDER BY MaterialF4ReceiveDate";
                string sql_delivery = "SELECT InvoiceID, MaterialPart, DeliveryQuantity, DeliveryDate, Remark"
                    + " FROM [444_MaterialF4Delivery] WHERE MaterialPart = '" + this.partNo.Text + "' ORDER BY DeliveryDate";
                string sql_summary = "SELECT ISNULL(SUM(t1.InStore), 0) AS InStore, ISNULL(SUM(t1.NotReceive), 0) AS NotReceive,"
                    + " ISNULL(SUM(t1.TotalOrder), 0) AS TotalOrder, ISNULL(SUM(t1.TotalReceive), 0) AS TotalReceive,"
                    + " ISNULL(SUM(t1.TotalDelivery), 0) AS TotalDelivery"
                    + " FROM"
                    + " (SELECT ISNULL(RemainQuantity, 0) AS InStore, 0 AS NotReceive, 0 AS TotalOrder, 0 AS TotalReceive, 0 AS TotalDelivery"
                    + " FROM [VF4MaterialStore]"
                    + " WHERE MaterialID = '" + this.partNo.Text + "'"
                    + " UNION ALL"
                    + " SELECT 0 AS InStore, ISNULL(InOrderQuantity, 0) AS NotReceive, 0 AS TotalOrder, 0 AS TotalReceive, 0 AS TotalDelivery"
                    + " FROM [VF4MaterialNotReceive]"
                    + " WHERE MaterialID = '" + this.partNo.Text + "'"
                    + " UNION ALL"
                    + " SELECT 0 AS InStore, 0 AS NotReceive, ISNULL(SUM(Quantity), 0) AS TotalOrder, 0 AS TotalReceive, 0 AS TotalDelivery"
                    + " FROM [444_MaterialOrderDetail]"
                    + " GROUP BY MaterialID"
                    + " HAVING MaterialID = '" + this.partNo.Text + "'"
                    + " UNION ALL"
                    + " SELECT 0 AS InStore, 0 AS NotReceive, 0 AS TotalOrder, ISNULL(SUM(MaterialF4ReceiveQuantity), 0) AS TotalReceive, 0 AS TotalDelivery"
                    + " FROM [444_MaterialF4Receive]"
                    + " GROUP BY MaterialID"
                    + " HAVING MaterialID = '" + this.partNo.Text + "'"
                    + " UNION ALL"
                    + " SELECT 0 AS InStore, 0 AS NotReceive, 0 AS TotalOrder, 0 AS TotalReceive, ISNULL(SUM(DeliveryQuantity), 0) AS TotalDelivery"
                    + " FROM [444_MaterialF4Delivery]"
                    + " GROUP BY MaterialPart"
                    + " HAVING MaterialPart = '" + this.partNo.Text + "') AS t1";

                F4MaterialOrder.Columns.Clear();
                gridF4MaterialOrder.DataSource = null;
                orderDataSet = mySQLManagement.SQL_Query(sql_order);
                if (orderDataSet != null)
                {
                    gridF4MaterialOrder.DataSource = orderDataSet.Tables[0];
                }
                else
                {
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("OrderNo", typeof(string));
                    newTable.Columns.Add("MaterialID", typeof(string));
                    newTable.Columns.Add("Quantity", typeof(float));
                    newTable.Columns.Add("DeadLine", typeof(DateTime));
                    gridF4MaterialOrder.DataSource = newTable;
                }
                F4MaterialOrder.Columns[0].Caption = "Order No";
                F4MaterialOrder.Columns[1].Caption = "Material No";
                F4MaterialOrder.Columns[2].Caption = "Quantity";
                F4MaterialOrder.Columns[3].Caption = "Dead Line";
                F4MaterialOrder.BestFitColumns();

                F4MaterialReceive.Columns.Clear();
                gridF4MaterialReceive.DataSource = null;
                receiveDataSet = mySQLManagement.SQL_Query(sql_receive);
                if (receiveDataSet != null)
                {
                    gridF4MaterialReceive.DataSource = receiveDataSet.Tables[0];
                }
                else
                {
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("InvoiceNo", typeof(string));
                    newTable.Columns.Add("MaterialID", typeof(string));
                    newTable.Columns.Add("Quantity", typeof(float));
                    newTable.Columns.Add("ReceiveDate", typeof(DateTime));
                    gridF4MaterialReceive.DataSource = newTable;
                }
                F4MaterialReceive.Columns[0].Caption = "Invoice No";
                F4MaterialReceive.Columns[1].Caption = "Material No";
                F4MaterialReceive.Columns[2].Caption = "Quantity";
                F4MaterialReceive.Columns[3].Caption = "Receive Date";
                F4MaterialReceive.BestFitColumns();

                F4MaterialDelivery.Columns.Clear();
                gridF4MaterialDelivery.DataSource = null;
                deliveryDataSet = mySQLManagement.SQL_Query(sql_delivery);
                if (deliveryDataSet != null)
                {
                    gridF4MaterialDelivery.DataSource = deliveryDataSet.Tables[0];
                }
                else
                {
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("InvoiceNo", typeof(string));
                    newTable.Columns.Add("MaterialID", typeof(string));
                    newTable.Columns.Add("Quantity", typeof(float));
                    newTable.Columns.Add("DeliveryDate", typeof(DateTime));
                    gridF4MaterialDelivery.DataSource = newTable;
                }
                F4MaterialDelivery.Columns[0].Caption = "Invoice No";
                F4MaterialDelivery.Columns[1].Caption = "Material No";
                F4MaterialDelivery.Columns[2].Caption = "Quantity";
                F4MaterialDelivery.Columns[3].Caption = "Delivery Date";
                F4MaterialDelivery.BestFitColumns();

                summaryDataSet = mySQLManagement.SQL_Query(sql_summary);
                if (summaryDataSet != null)
                {
                    this.inStore = summaryDataSet.Tables[0].Rows[0]["InStore"].ToString();
                    this.notReceive = summaryDataSet.Tables[0].Rows[0]["NotReceive"].ToString();
                    this.totalOrder = summaryDataSet.Tables[0].Rows[0]["TotalOrder"].ToString();
                    this.totalReceive = summaryDataSet.Tables[0].Rows[0]["TotalReceive"].ToString();
                    this.totalDelivery = summaryDataSet.Tables[0].Rows[0]["TotalDelivery"].ToString();
                }
                
                this.F4MaterialOrder.ViewCaption = "Order: -Total Order Quantity: " + this.totalOrder;
                this.F4MaterialReceive.ViewCaption = "Receive: - Total Receive: " + this.totalReceive + "; - Not Receive: " + this.notReceive;
                this.F4MaterialDelivery.ViewCaption = "Delivery: - Total Delivery: " + this.totalDelivery + "; - In Store: " + this.inStore;
            }
        }
    }
}
